USE [NANS21V]
GO

/****** オブジェクト:  StoredProcedure [dbo].[ResultEkidenHosei]    スクリプト日付: 11/08/2024 10:07:02 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ResultEkidenHosei]') AND type in  (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[ResultEkidenHosei]
GO

CREATE FUNCTION [dbo].[ResultEkidenHosei] (
	@TAIKAINO int,
	@KYOGINO int,
	@LANE int,
    @SOUJUN int/* ひとつ前の走順を呼び出す */
)  
RETURNS int
AS
BEGIN
	DECLARE @NEXT int;
	DECLARE @ADD int;
	SET @NEXT = @SOUJUN -1;
	SET @ADD = 0;
	IF (@NEXT > 0) EXEC @ADD = dbo.ResultEkidenHosei @TAIKAINO ,@KYOGINO ,@LANE ,@NEXT
    RETURN (
		SELECT ISNULL(FLOOR(ST.KIROKU + RSI.KIROKU - NS.KIROKU),0) + @ADD
		FROM TBL_RESULT_SIGI AS RSI 
		LEFT JOIN TBL_RESULT_SIGI AS ST ON RSI.TAIKAINO = ST.TAIKAINO /* 開始時間用 */
    		AND RSI.KYOGINO = ST.KYOGINO AND RSI.LANE = ST.LANE 
    		AND ST.SIGINO = 1 
		LEFT JOIN TBL_RESULT_SIGI AS NS ON RSI.TAIKAINO = NS.TAIKAINO /* 次の開始 */
    		AND RSI.KYOGINO = NS.KYOGINO AND RSI.LANE = NS.LANE 
    		AND @SOUJUN *2+1 = NS.SIGINO 

		WHERE RSI.TAIKAINO = @TAIKAINO AND RSI.KYOGINO = @KYOGINO /* 通過記録 */
    		AND RSI.LANE   = @LANE     AND RSI.SIGINO = @SOUJUN *2 
		)

END

